package com.kamikaze;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Clock {

	// 寫入打卡資料所需變數
	Integer shift_number;
	java.sql.Date clock_datetime;
	
	// 查詢班表編號所需變數
	String user_id;
	java.sql.Date shift_date;
	
	// 判斷打上班或下班卡所需變數
	String clock_type;
		
	// 資料庫讀取寫入所需函數
	String classname = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze";
	String check_sn_sql = "select * from shift where user_id = ? and shift_date = ?";
	String clock_sql;
	String check_clock_sql = "select * from clockdata where shift_number = ?";
	
	// 判斷是否打過卡
	boolean clocked_in = false;
	boolean clocked_out = false;
	
	// 將訊息傳回打卡頁面
	ArrayList<String> msg = new ArrayList<String>();
	
	// 空建構子
	public Clock() {}

	
	// 表單傳來的變數建構子
	public Clock(String user_id, String clock_type) {
		this.user_id = user_id;
		this.clock_type = clock_type;
		getShift_date();
		getSn();
		getClock_sql();
		checkClocked();
		if (!clocked_out) {
			if (clock_type.equals("1") && clocked_in){
				 msg.add("你已經打過上班卡！");
			 } else {
				 writeClock();
			 }
		} else {
			msg.add("你已經打過下班卡！");
		}
	}
	
	// 判斷是打上班卡還是下班卡
	public String getClock_sql() {
		switch (clock_type) {
		case "1":
			clock_sql = "update clockdata set clock_in = now() where shift_number = ?";
			break;
		case "2":
			clock_sql = "update clockdata set clock_out = now() where shift_number = ?";
			break;
		}
		return clock_sql;
	}
	
	// 將打卡時間寫入資料庫
	public void writeClock() {
		
		try {
			Class.forName(classname);
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(clock_sql);
			pstmt.setInt(1, shift_number);
			int row = pstmt.executeUpdate();
			System.out.println("寫入 " + row + " 筆資料");
			if (row == 1) {
				if (clock_type.equals("1")) {
					msg.add("上班打卡成功");
				} else {
					msg.add("下班打卡成功");
				}
			}
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 取得該使用者當天的班表編號
	public Integer getSn() {
		try {
			Class.forName(classname);
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(check_sn_sql);
			pstmt.setString(1, user_id);
			pstmt.setDate(2, shift_date);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				shift_number = rs.getInt("shift_number");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return shift_number;
	}
	
	// 查詢上下班打卡是否有資料
	
	public void checkClocked() {
		
		try {
			Class.forName(classname);
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(check_clock_sql);
			pstmt.setInt(1, shift_number);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				if (rs.getString("clock_in") != null) {
					clocked_in = true;
				}
				if (rs.getString("clock_out") != null) {
					clocked_out = true;
				}
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// getter & setter
	public Integer getShift_number() {
		return shift_number;
	}

	public void setShift_number(Integer shift_number) {
		this.shift_number = shift_number;
	}

	public java.sql.Date getClock_datetime() {
		return clock_datetime;
	}

	public void setClock_datetime(java.sql.Date clock_datetime) {
		this.clock_datetime = clock_datetime;
	}

	public String getUser_id() {
		return user_id;
	}

	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}

	public java.sql.Date getShift_date() {
		java.util.Date now = new java.util.Date();
		java.text.DateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd");
		shift_date = java.sql.Date.valueOf(df.format(now));
		return shift_date;
	}

	public void setShift_date(java.sql.Date shift_date) {
		this.shift_date = shift_date;
	}

	public String getClock_type() {
		return clock_type;
	}

	public void setClock_type(String clock_type) {
		this.clock_type = clock_type;
	}

	public ArrayList<String> getMsg() {
		return msg;
	}

	public void setMsg(ArrayList<String> msg) {
		this.msg = msg;
	}

	public boolean isClocked_in() {
		return clocked_in;
	}

	public void setClocked_in(boolean clocked_in) {
		this.clocked_in = clocked_in;
	}

	public boolean isClocked_out() {
		return clocked_out;
	}

	public void setClocked_out(boolean clocked_out) {
		this.clocked_out = clocked_out;
	}

}